Updating Data

Updating Data#

We can use the UPDATE statement to change the value of a column for a row or multiple rows.

Example Syntax#

UPDATE table

SET col1 = val1, col2 = val2, … coln = valn

WHERE <condition>

ORDER BY col5

LIMIT 5;

Connect to the terminal below by clicking in the widget. Once connected, the command line prompt will show up. Enter or copy-paste the command ./DataJek/Lessons/15lesson.sh and wait for the mysql prompt to start-up.

1. Say Bernie Sanders wins the US Presidential elections and caps the maximum net worth for any individual to be no more than a million dollars. We’d want to update our Actors table in response because all the actors are now worth just one million dollars. The following query modifies the NetWorthInMillions column for all the rows in the table.

   UPDATE Actors SET NetWorthInMillions=1;
   

We can use LIMIT and ORDER BY in conjunction to restrict the effects of the update statement. Say, we want to increase the net worth of the first three actors, sorted by first name, to five million dollars. We could achieve that as follows:

UPDATE Actors SET NetWorthInMillions=5 ORDER BY FirstName LIMIT 3;

2. The UPDATE statement has two parts. The matching phase and then the modification phase. In the first phase the row/rows that match the query are determined and in the second phase the changes are applied. If we re-execute the update query from the previous step, the count of the number of rows matched will be three but the row count for changes will be zero.

   UPDATE Actors SET NetWorthInMillions=5 ORDER BY FirstName LIMIT 3;
   

3. Lastly, we can update multiple columns in an UPDATE statement. Say we want to give all the actors fifty million dollars and make them single, we can do so in a single UPDATE statement.

   UPDATE Actors SET NetWorthInMillions=50, MaritalStatus="Single";